from database.connection import execute_query
from datetime import datetime
import re

# 输入验证函数
def validate_email(email):
    if not re.match(r"[^@]+@[^@]+\.[^@]+", email):
        raise ValueError("Invalid email format")

def validate_date(date_str):
    try:
        datetime.strptime(date_str, '%Y-%m-%d')
    except ValueError:
        raise ValueError("Invalid date format (YYYY-MM-DD)")

# Patients CRUD
def create_patient(name, dob, contact, email, medical_history):
    validate_email(email)
    validate_date(dob)
    if not name:
        raise ValueError("Name cannot be empty")
    query = """
        INSERT INTO Patients (name, dob, contact, email, medical_history)
        VALUES (%s, %s, %s, %s, %s)
    """
    execute_query(query, (name, dob, contact, email, medical_history))

def read_patients(search_name=None):
    if search_name:
        query = "SELECT * FROM Patients WHERE name ILIKE %s"
        params = (f"%{search_name}%",)
    else:
        query = "SELECT * FROM Patients"
        params = None
    return execute_query(query, params, fetch=True)

def update_patient(patient_id, name=None, dob=None, contact=None, email=None, medical_history=None):
    fields = []
    params = []
    if name: fields.append("name = %s"); params.append(name)
    if dob: validate_date(dob); fields.append("dob = %s"); params.append(dob)
    if contact: fields.append("contact = %s"); params.append(contact)
    if email: validate_email(email); fields.append("email = %s"); params.append(email)
    if medical_history: fields.append("medical_history = %s"); params.append(medical_history)
    if not fields:
        raise ValueError("No fields to update")
    query = f"UPDATE Patients SET {', '.join(fields)} WHERE patient_id = %s"
    params.append(patient_id)
    execute_query(query, tuple(params))

def delete_patient(patient_id):
    query = "DELETE FROM Patients WHERE patient_id = %s"
    execute_query(query, (patient_id,))

# Appointments CRUD (with doctor availability check)
def create_appointment(patient_id, staff_id, room_id, date, time):
    validate_date(date)
    conn = get_connection()
    try:
        with conn.cursor() as cur:
            cur.execute("SELECT check_doctor_availability(%s, %s, %s::TIME)", (staff_id, date, time))
            available = cur.fetchone()[0]
            if not available:
                raise ValueError("Doctor not available at that time")
            query = """
                INSERT INTO Appointments (patient_id, staff_id, room_id, date, time)
                VALUES (%s, %s, %s, %s, %s::TIME)
            """
            cur.execute(query, (patient_id, staff_id, room_id, date, time))
            conn.commit()
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()

def read_appointments(by_date=None, by_doctor=None, by_patient=None):
    query = "SELECT * FROM Appointments"
    params = []
    if by_date:
        query += " WHERE date = %s"
        params.append(by_date)
    elif by_doctor:
        query += " WHERE staff_id = %s"
        params.append(by_doctor)
    elif by_patient:
        query += " WHERE patient_id = %s"
        params.append(by_patient)
    return execute_query(query, tuple(params) if params else None, fetch=True)

def update_appointment(appointment_id, date=None, time=None):
    fields = []
    params = []
    if date: validate_date(date); fields.append("date = %s"); params.append(date)
    if time: fields.append("time = %s::TIME"); params.append(time)
    if not fields:
        raise ValueError("No fields to update")
    query = f"UPDATE Appointments SET {', '.join(fields)} WHERE appointment_id = %s"
    params.append(appointment_id)
    execute_query(query, tuple(params))

def delete_appointment(appointment_id):
    query = "DELETE FROM Appointments WHERE appointment_id = %s"
    execute_query(query, (appointment_id,))

# Medical_Records CRUD
def create_medical_record(appointment_id, diagnosis, symptoms, observations):
    if not diagnosis:
        raise ValueError("Diagnosis cannot be empty")
    query = """
        INSERT INTO Medical_Records (appointment_id, diagnosis, symptoms, observations)
        VALUES (%s, %s, %s, %s)
    """
    execute_query(query, (appointment_id, diagnosis, symptoms, observations))

def read_medical_records(patient_id=None):
    if patient_id:
        query = """
            SELECT mr.* FROM Medical_Records mr
            JOIN Appointments a ON mr.appointment_id = a.appointment_id
            WHERE a.patient_id = %s
        """
        params = (patient_id,)
    else:
        query = "SELECT * FROM Medical_Records"
        params = None
    return execute_query(query, params, fetch=True)

def update_medical_record(record_id, diagnosis=None, symptoms=None, observations=None):
    fields = []
    params = []
    if diagnosis: fields.append("diagnosis = %s"); params.append(diagnosis)
    if symptoms: fields.append("symptoms = %s"); params.append(symptoms)
    if observations: fields.append("observations = %s"); params.append(observations)
    if not fields:
        raise ValueError("No fields to update")
    query = f"UPDATE Medical_Records SET {', '.join(fields)} WHERE record_id = %s"
    params.append(record_id)
    execute_query(query, tuple(params))

def delete_medical_record(record_id):
    query = "DELETE FROM Medical_Records WHERE record_id = %s"
    execute_query(query, (record_id,))

# Prescriptions CRUD
def create_prescription(appointment_id, notes):
    query = "INSERT INTO Prescriptions (appointment_id, notes) VALUES (%s, %s)"
    execute_query(query, (appointment_id, notes))

def read_prescriptions(appointment_id=None):
    if appointment_id:
        query = "SELECT * FROM Prescriptions WHERE appointment_id = %s"
        params = (appointment_id,)
    else:
        query = "SELECT * FROM Prescriptions"
        params = None
    return execute_query(query, params, fetch=True)

def update_prescription(prescription_id, notes=None):
    if not notes:
        raise ValueError("No fields to update")
    query = "UPDATE Prescriptions SET notes = %s WHERE prescription_id = %s"
    execute_query(query, (notes, prescription_id))

def delete_prescription(prescription_id):
    query = "DELETE FROM Prescriptions WHERE prescription_id = %s"
    execute_query(query, (prescription_id,))

# Invoices CRUD
def create_invoice(appointment_id, amount):
    if amount <= 0:
        raise ValueError("Amount must be positive")
    query = "INSERT INTO Invoices (appointment_id, amount) VALUES (%s, %s)"
    execute_query(query, (appointment_id, amount))

def read_invoices(patient_id=None):
    if patient_id:
        query = """
            SELECT i.* FROM Invoices i
            JOIN Appointments a ON i.appointment_id = a.appointment_id
            WHERE a.patient_id = %s AND i.status = 'unpaid'
        """
        params = (patient_id,)
    else:
        query = "SELECT * FROM Invoices"
        params = None
    return execute_query(query, params, fetch=True)

def update_invoice(invoice_id, amount=None, status=None):
    fields = []
    params = []
    if amount: fields.append("amount = %s"); params.append(amount)
    if status: fields.append("status = %s"); params.append(status)
    if not fields:
        raise ValueError("No fields to update")
    query = f"UPDATE Invoices SET {', '.join(fields)} WHERE invoice_id = %s"
    params.append(invoice_id)
    execute_query(query, tuple(params))

def delete_invoice(invoice_id):
    query = "DELETE FROM Invoices WHERE invoice_id = %s"
    execute_query(query, (invoice_id,))